﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Bilyteam_Website;
using ClassLibrary1.Entity;

namespace ClassLibrary1.DAO
{
    public class TeamsDAO
    {
        public TeamsDAO()
        {

        }
        //SQL Queries
        public const string SQL_SELECT_TEAMS = "SELECT * FROM Teams WHERE Status = 0 ORDER BY TeamsID ";
        public const string SQL_SELECT_BY_ID = "SELECT * FROM Teams Where TeamsID=@TeamsID";
        public const string SQL_INSERT_TEAMS = "INSERT INTO Teams(TeamName,TeamEmail,TotalEmail,Status)VALUES (@TeamName,@TeamEmail,@TotalEmail,@Status)";
        public const string SQL_UPDATE_TEAMS = "UPDATE Teams SET TeamName=@TeamName,TeamEmail=@TeamEmail,TotalEmail=@TotalEmail Where TeamsID=@TeamsID";
        public const string SQL_DELETE_TEAMS = "UPDATE Teams SET Status=@Status WHERE TeamsID=@TeamsID";

        //Parameter
        public const string PARM_TeamsID = "@TeamsID";
        public const string PARM_TeamName = "@TeamName";
        public const string PARM_TeamEmail = "@TeamEmail";
        public const string PARM_TotalEmail = "@TotalEmail";
        public const string PARM_Status = "@Status";

        private Teams TeamManipulate(SqlDataReader reader)
        {
            Teams tem = new Teams();

            if (!reader.IsDBNull(0))
                tem.TeamsID = reader.GetInt32(0);
            if (!reader.IsDBNull(1))
                tem.TeamName = reader.GetString(1);
            if (!reader.IsDBNull(2))
                tem.TeamEmail = reader.GetString(2);
            if (!reader.IsDBNull(3))
                tem.TotalEmail = reader.GetInt32(3);
            if (!reader.IsDBNull(4))
                tem.Status = reader.GetBoolean(4);
            return tem;
        }
        public IList<Teams> GetAll()
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_Status, SqlDbType.Bit)};
            parms[0].Value = 0;
            IList<Teams> temlist = new List<Teams>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_TEAMS, parms))
            {
                while (reader.Read())
                {
                    Teams tem = TeamManipulate(reader);
                    temlist.Add(tem);
                }
            }
            return temlist;
        }
        public Teams GetTeamById(int TeamsID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_TeamsID, SqlDbType.Int) };
            parms[0].Value = TeamsID;

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_BY_ID, parms))
            {
                if (reader.Read())
                {
                    Teams dep = TeamManipulate(reader);
                    return dep;
                }
            }
            return null;
        }

        public bool Insert(Teams tem)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[]{
               
                new SqlParameter(PARM_TeamName,SqlDbType.NVarChar),
                new SqlParameter(PARM_TeamEmail,SqlDbType.NVarChar),
                new SqlParameter(PARM_TotalEmail,SqlDbType.Int),
                new SqlParameter(PARM_Status,SqlDbType.Bit)};

            parms[0].Value = tem.TeamName;
            parms[1].Value = tem.TeamEmail;
            parms[2].Value = tem.TotalEmail;
            parms[3].Value = tem.Status;

            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn,
                    CommandType.Text, SQL_INSERT_TEAMS, parms);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {

                throw e;
            }
            return false;
        }
        public bool Updae(Teams tem)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[]{
               new SqlParameter(PARM_TeamsID,SqlDbType.Int),
                new SqlParameter(PARM_TeamName,SqlDbType.NVarChar),
                new SqlParameter(PARM_TeamEmail,SqlDbType.NVarChar),
                new SqlParameter(PARM_TotalEmail,SqlDbType.Int),
                new SqlParameter(PARM_Status,SqlDbType.Bit)};
            parms[0].Value = tem.TeamsID;
            parms[1].Value = tem.TeamName;
            parms[2].Value = tem.TeamEmail;
            parms[3].Value = tem.TotalEmail;
            parms[4].Value = tem.Status;
            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn,
                    CommandType.Text, SQL_UPDATE_TEAMS, parms);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {

                throw e;
            }
            return false;

        }
        public bool Delete(int id)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[]{new SqlParameter(PARM_TeamsID,SqlDbType.Int),
                                       new SqlParameter(PARM_Status ,SqlDbType.Bit) };
            parms[0].Value = id;
            parms[1].Value = 1;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                                                     CommandType.Text, SQL_DELETE_TEAMS, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {

                throw e;
            }
            return false;
        }

    }


}